#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
${HIVE_HOME} -S -e

--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;


insert into table  dwd_dim_customer_appeal
SELECT
  id ,
    customer_relationship_first_id ,
    employee_id ,
    employee_name ,
    employee_department_id ,
    employee_tdepart_id ,
    appeal_status ,
    dt
from edu_ods.ods_customer_appeal;


insert into table  dwd_dim_employee
SELECT
    id  ,
    real_name  ,
    phone  ,
    department_id    ,
    department_name    ,
    job_number       ,
    last_login_date    ,
    creator  ,
    create_date_time  ,
    update_date_time,
    deleted,
    scrm_department_id  ,
    superior_leaders_id ,
    tdepart_id ,
    ems_user_name,
    dt
from edu_ods.ods_employee;

insert into table  dwd_dim_itcast_clazz
SELECT
   id,
    create_date_time   ,
    update_date_time ,
    deleted ,
    itcast_school_id  ,
    itcast_school_name ,
    itcast_subject_id ,
    itcast_subject_name ,
    itcast_brand  ,
    clazz_type_state ,
    clazz_type_name ,
    teaching_mode ,
    start_time ,
    end_time ,
    comment ,
    dt
from  edu_ods.ods_itcast_clazz;

insert into table  dwd_dim_scrm_department
SELECT
    id  ,
    name ,
    parent_id ,
    create_date_time ,
    update_date_time ,
    tdepart_code ,
    creator  ,
    depart_level,
    depart_sign,
    dt
  from edu_ods.ods_scrm_department;

insert into table  dwd_dim_customer_clue
SELECT
    id  ,
    create_date_time  ,
    update_date_time  ,
    customer_id ,
    customer_relationship_id ,
    itcast_school_id  ,
    itcast_school    ,
    itcast_subject_id  ,
    itcast_subject   ,
    level    ,
    origin_type ,
    valid ,
    clue_state ,
    dt
 from edu_ods.ods_customer_clue;

insert into table  dwd_fact_customer_relationship PARTITION(start_date)
SELECT
    id              ,
    create_date_time  ,
    deleted  ,
    customer_id       ,
    origin_type      ,
    itcast_school_id  ,
    itcast_subject_id ,
    creator          ,
    current_creator  ,
    creator_name     ,
    origin_channel    ,
    payment_state  ,
    payment_time    ,
    course_id       ,
    course_name    ,
    appeal_id     ,
    '9999-12-31'end_date,
    dt as start_date
from edu_ods.ods_customer_relationship;

-- --sqoop增量采集 （新增和更新采集----->ODS（2023-05-07））   增量表 --->结果集2
-- /usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
-- --connect 'jdbc:mysql://106.75.33.59:3306/scrm?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
-- --username itcast_edu_stu \
-- --password itcast_edu_stu \
-- --query "select *,'2023-05-06' as dt from customer_clue where 1=1 and(create_time between '2023-05-07 00:00:00' and '2023-05-07 23:59:59') or (update_time between '2023-05-07 00:00:00' and '2023-05-07') and  \$CONDITIONS" \
-- --hcatalog-database edu_ods \
-- --hcatalog-table ods_customer_relationship \
-- -m 1

set hive.exec.dynamic.partition.mode=nonstrict;

insert overwrite table dwd_fact_customer_relationship_linshi partition(start_date)
select
    a.id              ,
    a.create_date_time  ,
    a.deleted ,
    a.customer_id       ,
    a.origin_type      ,
    a.itcast_school_id  ,
    a.itcast_subject_id ,
    a.creator          ,
    a.current_creator  ,
    a.creator_name     ,
    a.origin_channel    ,
    a.payment_state  ,
    a.payment_time    ,
    a.course_id       ,
    a.course_name    ,
    a.appeal_id     ,
    --todo end_date是否修改 取决于join的结果
    if(b.id is null or a.end_date <'9999-99-99',a.end_date,date_sub(b.start_date,1)) as end_date,
    a.start_date
from dwd_fact_customer_relationship a left join (select * from dwd_fact_customer_relationship where start_date ='2023-05-06') b
on a.id = b.id
union all
select
    id              ,
    create_date_time  ,
    deleted,
    customer_id       ,
    origin_type      ,
    itcast_school_id  ,
    itcast_subject_id ,
    creator          ,
    current_creator  ,
    creator_name     ,
    origin_channel    ,
    payment_state  ,
    payment_time    ,
    course_id       ,
    course_name    ,
    appeal_id     ,
    '9999-12-31'as end_date,
    start_date as start_date
from dwd_fact_customer_relationship where start_date ='2021-11-30';

create table  edu_dwd.dwd_fact_customer_relationship_linshi (

    id                          INT COMMENT '意向id',
    create_date_time            STRING COMMENT '创建时间',
    deleted                     TINYINT COMMENT '是否被删除（禁用）',
    customer_id                 INT COMMENT '所属客户id',
    origin_type                 STRING COMMENT '数据来源',
    itcast_school_id            INT COMMENT '校区Id',
    itcast_subject_id           INT COMMENT '学科Id',
    creator                     INT COMMENT '创建人',
    current_creator             INT COMMENT '当前创建人：初始==创建人，当在公海拉回时为 拉回人',
    creator_name                STRING COMMENT '创建者姓名',
    origin_channel              STRING COMMENT '来源渠道',
    payment_state               STRING COMMENT '支付状态',
    payment_time                STRING COMMENT '支付状态变动时间',
    course_id                   INT COMMENT '课程ID',
    course_name                 STRING COMMENT '课程名称',
    appeal_id                   INT COMMENT '申诉id',
    end_date                          STRING
) partitioned by (start_date string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'SNAPPY');

insert overwrite table dwd_fact_customer_relationship partition(start_date)
select * from  edu_dwd.dwd_fact_customer_relationship_linshi;